from typing import Type
from sqlalchemy import func, and_
from sqlalchemy.orm import Session, joinedload
from server.models.achievement import *
from server.models.user import User, UserProfile
from server.schemas.achievement import *
from utils import ProjectException


def query_submitted_form_by_user_id(db: Session, user_id: int):
    """查询SubmittedForm表中指定user_id的内容"""
    return db.query(SubmittedForm).filter_by(user_id=user_id).all()


def query_submitted_form_total_count(db: Session, achievement_id: int, status: str = None) -> int:
    query = db.query(SubmittedForm).filter(
        SubmittedForm.achievement_id == achievement_id
    )

    # 仅在 status 不为空时添加筛选条件
    if status:
        query = query.filter(SubmittedForm.review_status == status)

    return query.options(
        joinedload(SubmittedForm.user).joinedload(User.profile).joinedload(UserProfile.student_class)
    ).count()


def query_submitted_form_by_achievement_id(db: Session, achievement_id: int, status: str = None, length: int = 20, page: int = 1) -> list[Type[SubmittedForm]]:
    """查询SubmittedForm表中指定achievement_id的内容"""
    offset = (page - 1) * length
    query = db.query(SubmittedForm).filter(
        SubmittedForm.achievement_id == achievement_id
    )

    # 仅在 status 不为空时添加筛选条件
    if status:
        query = query.filter(SubmittedForm.review_status == status)

    return query.options(
        joinedload(SubmittedForm.user).joinedload(User.profile).joinedload(UserProfile.student_class)
    ).offset(offset).limit(length).all()


def query_submitted_form_by_achievement_and_user(db: Session, achievement_id: int, user_id: int):
    """查询SubmittedFormContents表中指定achievement_id和user_id的内容"""
    return db.query(SubmittedForm).filter(
        SubmittedForm.user_id == user_id,
        SubmittedForm.achievement_id == achievement_id
    ).all()


def query_submitted_form_and_contents(db: Session, submitted_form_id: int) -> SubmittedForm:
    """通过submitted_form_id查询SubmittedForm表中关联的内容"""
    return db.query(SubmittedForm).filter(
        SubmittedForm.id == submitted_form_id
    ).options(
        joinedload(SubmittedForm.achievement),
        joinedload(SubmittedForm.contents).joinedload(SubmittedFormContent.form_rule),
        joinedload(SubmittedForm.contents).joinedload(SubmittedFormContent.reviews),
    ).first()


def update_review_status_and_add_review(db: Session, submitted_form_content_id: int, reviewer_id: int, review_comments: str, review_status: str):
    try:
        # 获取指定的SubmittedFormContent对象
        submitted_form_content = db.query(SubmittedFormContent).get(submitted_form_content_id)
        if not submitted_form_content:
            raise ProjectException("SubmittedFormContent not found")

        # 更新SubmittedFormContent的审核状态
        submitted_form_content.review_status = review_status

        # 添加新的Review记录
        review = Review(
            submitted_form_content_id=submitted_form_content.id,
            reviewer_id=reviewer_id,
            review_comments=review_comments,
            review_score=0
        )
        db.add(review)

        # 检查所有相关的SubmittedFormContent是否都已审核
        submitted_form = submitted_form_content.submitted_form
        all_contents_reviewed = all(
            content.review_status != 'pending' for content in submitted_form.contents
        )
        # 如果都已审核完毕，更新SubmittedForm的审核状态
        if all_contents_reviewed:

            submitted_form.review_status = 'approved' if all(
                content.review_status == 'approved' for content in submitted_form.contents
            ) else 'rejected'

        # 提交事务
        db.commit()
    except Exception as e:
        db.rollback()  # 回滚事务以防出错
        raise e
    finally:
        db.close()
